﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Services;
using System.Collections.Specialized;
using System.Data;
using System.Web.Script.Serialization;
using System.Data.OleDb;
using System.Data.SqlClient;
using Ebiz.libs;
using System.IO;
using System.Web.Script.Services;

namespace Ebiz
{
    public partial class PageHandler : System.Web.UI.Page
    {

        static DataTable dtExcel = new DataTable();
        static DataTable dtGridView = new DataTable();

        /// <summary>
        /// Get Data From Excel File
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        [WebMethod]
        public static string GetExcelData(string FileName)
        {
            FileName = HttpContext.Current.Server.MapPath("~/images/upload/") + FileName;
            CreateColumnDataTable();
            dtExcel = new DataTable();
            DataTable dt = new DataTable();
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;""";
            excelConnectionString = string.Format(excelConnectionString, FileName).Trim();

            // Create Connection to Excel Workbook
            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
            {
                OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
                DataSet ds = new DataSet();
                OleDbDataAdapter adp = new OleDbDataAdapter(command);
                adp.Fill(dtExcel);
                return dtExcel.Rows.Count.ToString();
            }
        }

        /// <summary>
        /// Create column
        /// </summary>
        public static void CreateColumnDataTable()
        {
            dtGridView = new DataTable("ClaimsDataTable");
            dtGridView.Columns.Add("IDData", typeof(string));
            dtGridView.Columns.Add("No", typeof(string));
            dtGridView.Columns.Add("NO FAKTUR", typeof(string));
            dtGridView.Columns.Add("#CUSTOMER", typeof(string));
            dtGridView.Columns.Add("#CUSTOMER (Ebiz)", typeof(string));
            dtGridView.Columns.Add("KODE PRODUK", typeof(string));
            dtGridView.Columns.Add("KODE PRODUK (Ebiz)", typeof(string));
            dtGridView.Columns.Add("%CLAIM", typeof(string));
            dtGridView.Columns.Add("#CLAIM", typeof(string));
            dtGridView.Columns.Add("GROSS", typeof(string));
            dtGridView.Columns.Add("UNIT", typeof(string));
            dtGridView.Columns.Add("CLAIM", typeof(string));
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static string CheckPerRow(string rowNumber)
        {
            DataTable dt = dtExcel.Copy();
          if (dtExcel.Columns[0].ColumnName == "No")
           // if(dtExcel.Columns[0].ColumnName=="KODE PELANGGAN DISTRIBUTOR")
            {
                DataRow[] rows = dtExcel.Select("No = " + rowNumber);
                SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
                string result = string.Empty;

                using (cn)
                {
                    try
                    {
                        cn.Open();
                        if (rows.Length == 1)
                        {
                            DataRow row = rows[0];
                            //Store Key
                            List<string> keyMars = new List<string>();
                            List<string> keyEbiz = new List<string>();

                            //Create row to dtexport
                            DataRow rowExport = dtGridView.NewRow();
                            rowExport["No"] = rowNumber;
                            result = rowNumber + "|";

                            //Invoice No
                            string noFakturExcel = row["NO FAKTUR"].ToString();
                            string customerExcel = row["#CUSTOMER"].ToString();
                            string kodeProdukExcel = row["KODE PRODUK"].ToString();
                            string claimExcel = row["#Claim"].ToString();

                            #region CheckData INVOCEREFF in MARS
                            /**/
                            string qryMars = "SELECT Top 1 INVOICEREF FROM v_mapClaimCheck WHERE INVOICEREF = '" + noFakturExcel + "'";
                            qryMars += " AND CustomerCode = '" + customerExcel + "' And ProductCode = '" + kodeProdukExcel + "'";
                            SqlCommand cmdMars = new SqlCommand(qryMars, cn);
                            SqlDataReader rdrDataMars = cmdMars.ExecuteReader();

                            //Kalo ada berarti tiga tiganya ada atau ketemu di mars kasih tanda "OK"
                            //Kalo ga ada berarti tiga tiganya dianggap tidak ada "Data Not Found"
                            if (rdrDataMars.HasRows)
                            {
                                keyMars.Add(noFakturExcel);
                                rowExport["NO FAKTUR"] = "OK";
                                result += "OK" + "|";

                                keyMars.Add(customerExcel);
                                rowExport["#CUSTOMER"] = "OK";
                                result += "OK" + "|";

                                keyMars.Add(kodeProdukExcel);
                                rowExport["KODE PRODUK"] = "OK";
                                result += "OK" + "|";

                            }
                            else
                            {
                                rowExport["NO FAKTUR"] = "Data Not Found";
                                result += "Data Not Found" + "|";

                                rowExport["#CUSTOMER"] = "Data Not Found";
                                result += "Data Not Found" + "|";

                                rowExport["KODE PRODUK"] = "Data Not Found";
                                result += "Data Not Found" + "|";
                            }
                            rdrDataMars.Close();
                            /**/
                            #endregion
                            #region CheckData CL Number in Ebiz
                            string qryEbiz = "select Top 1 a.CLNum from tbl_request a, [tbl_RequestProduct]  b, tbl_RequestCustomer c ";
                            qryEbiz += "where a.CLNum = b.CLNum and a.CLNum = c.CLNum ";
                            qryEbiz += "and a.CLNum = '" + claimExcel + "'";
                            qryEbiz += " AND c.CustCode = '" + customerExcel + "' And b.ProductCode = '" + kodeProdukExcel + "'";
                            SqlCommand cmdEbiz = new SqlCommand(qryEbiz, cn);
                            SqlDataReader rdrDataEbiz = cmdEbiz.ExecuteReader();

                            //Kalo ada berarti tiga tiganya ada atau ketemu di ebiz kasih tanda "OK"
                            //Kalo ga ada berarti tiga tiganya dianggap tidak ada "Data Not Found"
                            if (rdrDataEbiz.HasRows)
                            {
                                keyEbiz.Add(claimExcel);
                                rowExport["#Claim"] = "OK";
                                result += "OK" + "|";

                                keyEbiz.Add(customerExcel);
                                rowExport["#CUSTOMER (Ebiz)"] = "OK";
                                result += "OK" + "|";

                                keyEbiz.Add(kodeProdukExcel);
                                rowExport["KODE PRODUK (Ebiz)"] = "OK";
                                result += "OK" + "|";

                            }
                            else
                            {
                                rowExport["#Claim"] = "Data Not Found";
                                result += "Data Not Found" + "|";

                                rowExport["#CUSTOMER (Ebiz)"] = "Data Not Found";
                                result += "Data Not Found" + "|";

                                rowExport["KODE PRODUK (Ebiz)"] = "Data Not Found";
                                result += "Data Not Found" + "|";
                            }
                            rdrDataEbiz.Close();
                            #endregion
                            #region Check value Gross Sales, Discount Value and Quantity in MARS
                            /**/
                            //Check value Gross Sales, Discount Value and Quantity
                            if (keyMars.Count == 3)
                            {
                                //Gross Sales
                                qryMars = "SELECT Top 1 AMOUNT FROM v_mapClaimCheck ";
                                qryMars += " WHERE CustomerCode " + " = '" + keyMars[1] + "'";
                                qryMars += " AND ProductCode " + " = '" + keyMars[2] + "'";
                                qryMars += " AND INVOICEREF " + " = '" + keyMars[0] + "'";
                                SqlCommand cmdAmount = new SqlCommand(qryMars, cn);
                                SqlDataReader rdrAmount = cmdAmount.ExecuteReader();
                                if (rdrAmount.HasRows)
                                {
                                    while (rdrAmount.Read())
                                    {
                                        if (row["GROSS"].ToString() == rdrAmount["AMOUNT"].ToString())
                                        {
                                            rowExport["GROSS"] = "OK";
                                            result += "OK" + "|";
                                        }
                                        else
                                        {
                                            rowExport["GROSS"] = "Invalid Value";
                                            result += "Invalid Value" + "|";
                                        }
                                    }
                                }
                                else
                                {
                                    rowExport["GROSS"] = "Data Not Found";
                                    result += "Data Not Found" + "|";
                                }
                                rdrAmount.Close();

                                //Claim
                                qryMars = "SELECT Top 1 AMOUNT_DISC_PRINC FROM v_mapClaimCheck ";
                                qryMars += " WHERE CustomerCode " + " = '" + keyMars[1] + "'";
                                qryMars += " AND ProductCode " + " = '" + keyMars[2] + "'";
                                qryMars += " AND INVOICEREF " + " = '" + keyMars[0] + "'";
                                SqlCommand cmdAmountDisc = new SqlCommand(qryMars, cn);
                                SqlDataReader rdrAmountDisc = cmdAmountDisc.ExecuteReader();
                                if (rdrAmountDisc.HasRows)
                                {
                                    while (rdrAmountDisc.Read())
                                    {
                                        if (row["CLAIM"].ToString() == rdrAmountDisc["AMOUNT_DISC_PRINC"].ToString())
                                        {
                                            rowExport["CLAIM"] = "OK";
                                            result += "OK" + "|";
                                        }
                                        else
                                        {
                                            rowExport["CLAIM"] = "Invalid Value";
                                            result += "Invalid Value" + "|";
                                        }
                                    }
                                }
                                else
                                {
                                    rowExport["CLAIM"] = "Data Not Found";
                                    result += "Data Not Found" + "|";
                                }
                                rdrAmountDisc.Close();

                                //Quantity
                                qryMars = "SELECT Top 1 Quantity FROM v_mapClaimCheck ";
                                qryMars += " WHERE CustomerCode " + " = '" + keyMars[1] + "'";
                                qryMars += " AND ProductCode " + " = '" + keyMars[2] + "'";
                                qryMars += " AND INVOICEREF " + " = '" + keyMars[0] + "'";
                                SqlCommand cmdQty = new SqlCommand(qryMars, cn);
                                SqlDataReader rdrQty = cmdQty.ExecuteReader();
                                if (rdrQty.HasRows)
                                {
                                    while (rdrQty.Read())
                                    {
                                        if (row["UNIT"].ToString() == rdrQty["Quantity"].ToString())
                                        {
                                            rowExport["UNIT"] = "OK";
                                            result += "OK" + "|";
                                        }
                                        else
                                        {
                                            rowExport["UNIT"] = "Invalid Value";
                                            result += "Invalid Value" + "|";
                                        }
                                    }
                                }
                                else
                                {
                                    rowExport["UNIT"] = "Data Not Found";
                                    result += "Data Not Found" + "|";
                                }
                                rdrQty.Close();
                            }
                            /**/
                            #endregion
                            #region Check value Gross Sales, Discount Value and Quantity in Ebiz
                            if (keyEbiz != null)
                            {
                                if (keyEbiz.Count == 3)
                                {
                                    qryEbiz = "select Top 1 b.Discount from tbl_request a, [tbl_RequestProduct]  b, tbl_RequestCustomer c ";
                                    qryEbiz += "where a.CLNum = b.CLNum and a.CLNum = c.CLNum ";
                                    qryEbiz += "and a.CLNum = '" + claimExcel + "'";
                                    qryEbiz += " AND c.CustCode = '" + customerExcel + "' And b.ProductCode = '" + kodeProdukExcel + "'";

                                    SqlCommand cmdDiscEbiz = new SqlCommand(qryEbiz, cn);
                                    SqlDataReader rdrDiscEbiz = cmdDiscEbiz.ExecuteReader();
                                    if (rdrDiscEbiz.HasRows)
                                    {
                                        while (rdrDiscEbiz.Read())
                                        {
                                            string[] hasil = (rdrDiscEbiz["Discount"].ToString().Split(new string[] { "." }, StringSplitOptions.None));
                                            string isNumber = Func.Sanitize(rdrDiscEbiz["Discount"].ToString());
                                            int hsl = (hasil.Count()>0 ) ? int.Parse(hasil[0]) : 0;
                                            int Source = int.Parse(row["%CLAIM"].ToString());
                                                if (Source == hsl)
                                                {
                                                    rowExport["%CLAIM"] = "OK";
                                                    result += "OK" + "|";
                                                }
                                                else
                                                {
                                                    rowExport["%CLAIM"] = "Invalid Value";
                                                    result += "Invalid Value" + "|";
                                                }
                                            

                                        }
                                    }
                                    else
                                    {
                                        rowExport["%CLAIM"] = "Data Not Found";
                                        result += "Data Not Found" + "|";
                                    }
                                    rdrDiscEbiz.Close();
                                }
                                else
                                {
                                    rowExport["%CLAIM"] = "Data Not Found";
                                    result += "Data Not Found" + "|";
                                }
                            }
                            #endregion
                            #region viewDataExport
                            //string qryEbizE=string.Empty;
                            //qryEbizE = "select Top 1 b.Discount from tbl_request a, [tbl_RequestProduct]  b, tbl_RequestCustomer c ";
                            //qryEbizE += "where a.CLNum = b.CLNum and a.CLNum = c.CLNum ";
                            //qryEbizE += "and a.CLNum = '" + claimExcel + "'";
                            //qryEbizE += " AND c.CustCode = '" + customerExcel + "' And b.ProductCode = '" + kodeProdukExcel + "'";
                            //SqlCommand cmdDiscEbiz = new SqlCommand(qryEbizE, cn);
                            //        SqlDataReader rdrDiscEbiz = cmdDiscEbiz.ExecuteReader();
                            //        if (rdrDiscEbiz.HasRows)
                            //        {
                            //            while (rdrDiscEbiz.Read())
                            //            {
                            //                rowExport["#CUSTOMER (Ebiz)"] = rdrDiscEbiz["ClCustName"].ToString();
                            //                rowExport["KODE PRODUK"] = rdrDataEbiz["ProductCode"].ToString();
                            //                rowExport["KODE PRODUK (Ebiz)"] = rdrDataEbiz[""].ToString();
                            //                rowExport["%CLAIM"] = rdrDataEbiz[""].ToString();
                            //                rowExport["#CLAIM"] = rdrDataEbiz[""].ToString();
                            //                rowExport["GROSS"] = rdrDataEbiz[""].ToString();
                            //                rowExport["UNIT"] = rdrDataEbiz[""].ToString();
                            //                rowExport["CLAIM"] = rdrDataEbiz[""].ToString();
                            //            }
                            //        }
                            //rowExport["NO FAKTUR"] = noFakturExcel.ToString();
                            //rowExport["#CUSTOMER"] = customerExcel.ToString();
                            #endregion
                            rowExport["IDData"] = row["No"].ToString();
                            dtGridView.Rows.Add(rowExport);
                            if (int.Parse(rowNumber) == dtExcel.Rows.Count)
                            {
                                string path = HttpContext.Current.Server.MapPath("~/images/upload/");
                                string IDData = DateTime.Now.TimeOfDay.TotalMilliseconds.ToString();
                                dtGridView.WriteXml(path + "claims-" + IDData + ".xml", XmlWriteMode.WriteSchema);
                                return IDData;
                            }
                            return "success";
                        }
                    }
                    catch (Exception ex)
                    {
                        return "errorebiz:" + ex.Message;
                    }
                    finally
                    {
                        if (cn.State == ConnectionState.Open)
                        {
                            cn.Close();
                        }
                    }
                }
            }
            return "success";
        }
    }
}